package com.example.onenetdht11.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

import com.example.onenetdht11.entity.User;
import com.example.onenetdht11.util.DBOpenHelper;
import com.example.onenetdht11.util.DBUilts;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDao {
    public boolean login(String id,String password){

        String sql = "select * from myuser where 账号 = ? and 密码 = ?";

        Connection con = DBUilts.getSQLConnection("192.168.37.1","sa",
                "zny15128362328ll", "OneNet");

        try {
            PreparedStatement pst=con.prepareStatement(sql);

            pst.setString(1,id);
            pst.setString(2,password);

            if(pst.executeQuery().next()){

                return true;

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUilts.close(con);
        }

        return false;
    }

    public boolean register(User user){

        String sql = "insert into myuser(账号,密码,昵称,姓名,性别,年龄,身份证,邮箱) values (?,?,?,?,?,?,?,?)";

        Connection  con = DBUilts.getSQLConnection("192.168.37.1","sa", "zny15128362328ll"
                , "OneNet");

        try {
            PreparedStatement pst=con.prepareStatement(sql);

            pst.setString(1,user.getId());
            pst.setString(2,user.getPassword());
            pst.setString(3,user.getName());
            pst.setString(4,user.getUsername());
            pst.setString(5,user.getSex());
            pst.setInt(6,user.getAge());
            pst.setString(7,user.getIdcard());
            pst.setString(8,user.getEmail());

            int value = pst.executeUpdate();

            if(value>0){
                return true;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUilts.close(con);
        }
        return false;
    }


    public User findUser(String id){

        String sql = "select * from myuser where 账号 = ?";

        Connection  con = DBUilts.getSQLConnection("192.168.37.1","sa", "zny15128362328ll"
                , "OneNet");
        User user = null;
        try {
            PreparedStatement pst=con.prepareStatement(sql);

            pst.setString(1,id);

            ResultSet rs = pst.executeQuery();

            while (rs.next()){

                String iddb = rs.getString(1);
                String name = rs.getString(2);
                String username = rs.getString(3);
                String password  = rs.getString(4);
                int age = rs.getInt(5);
                String sex = rs.getString(6);
                String userId = rs.getString(7);
                String email = rs.getString(8);
                String maxtemp = rs.getString(9);
                String mintemp = rs.getString(10);
                user = new User(iddb,name,username,password,age,sex,userId,email,mintemp,maxtemp);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUilts.close(con);
        }

        return user;
    }

    public String getmaxtemp(String id){
        String sql = "select 最大温度 from myuser where 账号 = ?";
        String tempv = null;
        Connection con = DBUilts.getSQLConnection("192.168.37.1","sa",
                "zny15128362328ll", "OneNet");
        try {
            PreparedStatement pst=con.prepareStatement(sql);
            pst.setString(1,id);
            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                 tempv = rs.getString(1);
            }
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return tempv;
    }
    public String getmintemp(String id){
        String sql = "select 最小温度 from myuser where 账号 = ?";
        String tempv = null;
        Connection con = DBUilts.getSQLConnection("192.168.37.1","sa",
                "zny15128362328ll", "OneNet");
        try {
            PreparedStatement pst=con.prepareStatement(sql);
            pst.setString(1,id);
            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                tempv = rs.getString(1);
            }
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return tempv;
    }
    public void updatemaxtemp(String id,String tempvalue){

        String sql = "update myuser set 最大温度= ? where 账号= ?";

        Connection  con = DBUilts.getSQLConnection("192.168.37.1","sa", "zny15128362328ll"
                , "OneNet");

        try {
            PreparedStatement pst=con.prepareStatement(sql);
            pst.setString(1,tempvalue);
            pst.setString(2,id);
            int value = pst.executeUpdate();

            if(value>0){
                System.out.println("修改成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUilts.close(con);
        }
    }
    public void updatemintemp(String id,String tempvalue){

        String sql = "update myuser set 最小温度= ? where 账号= ?";

        Connection  con = DBUilts.getSQLConnection("192.168.37.1","sa", "zny15128362328ll"
                , "OneNet");

        try {
            PreparedStatement pst=con.prepareStatement(sql);
            pst.setString(1,tempvalue);
            pst.setString(2,id);

            int value = pst.executeUpdate();

            if(value>0){
                System.out.println("修改成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUilts.close(con);
        }
    }
    //用户注册账户
    public void insertUser(User user, Context context){
        DBOpenHelper dbOpenHelper = new DBOpenHelper(context,"myuser",null,1);
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("username",user.getUsername());
        values.put("password",user.getPassword());
        //数据库执行插入命令
        db.insert("myuser", null, values);
    }
}
